﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Library;
using DevExpress.XtraCharts;
using Lamex.Base;
using DevExpress.Utils;
using Lamex.Interface;
using DevExpress.Utils.Extensions;

namespace Lamex.mCost.UI
{
    public partial class frmCOST_Component_Analysis : frmBaseChild
    {
        DataTable dtTemp = null;
        DataTable dtChart = null;
        string strSql;
        DataTable dt;
        string strDate_Start, strDate_End, strHNI_Month;
        string strCOST_FACTORY, strCondition;

        public frmCOST_Component_Analysis()
        {
            InitializeComponent();
        }

        private void frmCOST_Component_Analysis_Resize(object sender, EventArgs e)
        {
            splitContainerControl1.SplitterPosition = this.Height -270;
            splitContainerControl2.SplitterPosition = this.Width / 2;
        }

        private void frmCOST_Component_Analysis_Load(object sender, EventArgs e)
        {
            try { base.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理

            #region dateEdit1格式化

            //dateEdit1只显示年
            //dateEdit1.Properties.CalendarView = DevExpress.XtraEditors.Repository.CalendarView.Vista;
            //dateEdit1.Properties.ShowToday = false;
            //dateEdit1.Properties.ShowMonthHeaders = false;
            //dateEdit1.Properties.VistaCalendarInitialViewStyle = DevExpress.XtraEditors.VistaCalendarInitialViewStyle.YearsGroupView;
            //dateEdit1.Properties.VistaCalendarViewStyle = DevExpress.XtraEditors.VistaCalendarViewStyle.YearsGroupView;
            //dateEdit1.Properties.Mask.EditMask = "yyyy";
            //dateEdit1.Properties.Mask.UseMaskAsDisplayFormat = true;

            //dateEdit1只显示年月
            dateEdit1.Properties.CalendarView = DevExpress.XtraEditors.Repository.CalendarView.Vista;
            dateEdit1.Properties.ShowToday = false;
            dateEdit1.Properties.ShowMonthHeaders = true;
            dateEdit1.Properties.VistaCalendarInitialViewStyle = DevExpress.XtraEditors.VistaCalendarInitialViewStyle.YearView;
            dateEdit1.Properties.VistaCalendarViewStyle = DevExpress.XtraEditors.VistaCalendarViewStyle.YearView;
            dateEdit1.Properties.Mask.EditMask = "yyyyMM";
            dateEdit1.Properties.Mask.UseMaskAsDisplayFormat = true;
            dateEdit1.EditValue = DateTime.Now;
            comboBoxEdit1.Text = "全厂";

            #endregion 

            Lamex.Common.IniFile ini = new Lamex.Common.IniFile(Application.StartupPath + "\\config\\SavePath.ini");
            string strRecord= ini.IniReadValue("ScaleRecord", "Record");
            if(!string.IsNullOrEmpty(strRecord))
            {
                trackBarControl1.Properties.Maximum = 100;
                trackBarControl1.Properties.Minimum = 0;
                trackBarControl1.Value = int.Parse(strRecord);
            }
        }

        private void sbtnView_Click(object sender, EventArgs e)
        {
            //此模块涉及以下表
            //[172.23.134.36].[LamexMESManager].[dbo].[Transfer_101_Inside_record]    入仓记录
            //[172.23.134.36].[LamexMESManager].[Cost_HNI_Calendar]     HNI日历
            //[172.23.134.41].[LamexMES_TransferTP].[Transfer_Package_MO_TP]    MO TP
            //[172.23.134.41].[LamexMES_TransferTP].[Cost_Labor_Comp_Daily]     每日薪资成本
            //[172.23.134.41].[LamexMES_TransferTP].[Cost_Social_Insurance_Monthly] 每月社保公积金

            //可先在36上测试，最终在41上

            strHNI_Month = dateEdit1.Text.ToString();
            strCOST_FACTORY = comboBoxEdit1.Text.ToString();

            chartControl1.Series.Clear();
            if(string.IsNullOrEmpty(strDate_Start)|| string.IsNullOrEmpty(strDate_End))
            {
                XtraMessageBox.Show("无法获取起始日或结束日，请核实！");
                return;
            }

            if (string.IsNullOrEmpty(comboBoxEdit1.Text) || string.IsNullOrEmpty(dateEdit1.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("分厂及HNI月不允许为空", "提示", MessageBoxButtons.OK);
                return;
            }
            else
            {
                resetUI();
                //刷新汇总表
                addGridData_By_Sum();
                TotalSum();

                //刷新明细表
                addGridData_By_Detail();
                TotalSum();

                //图表数据加载
                //需求见\\Tx-fs10\tx-fs1\LAMEX\MES\Project\Comp Cost\Comp cost% 项目需求描述.pptx
                ShowChart();
            }
            
        }

        private void addGridData_By_Sum()
        {
            if (strCOST_FACTORY == "全厂")
            {
                //strCondition = " WHERE 1=1  and ISNULL(e.COST_FACTORY,'')!='' ";//图表不显示空值
                strCondition = " WHERE 1=1";
            }
            else
            {
                //strCondition = " WHERE e.COST_FACTORY='" + strCOST_FACTORY + @"'  and ISNULL(e.COST_FACTORY,'')!='' ";//图表不显示空值
                strCondition = " WHERE e.COST_FACTORY='" + strCOST_FACTORY + @"'";
            }

            #region 此SQL供无41数据库访问权限测试用户用
            //            strSql = @"SELECT e.COST_FACTORY ,convert(int,Salary_Cost) Salary_Cost,convert(int,Extra_Cost) Extra_Cost,convert(int,Comp_Cost) Comp_Cost,convert(int,Comp_TP) Comp_TP,    convert( varchar,Convert(decimal(18,2),( Comp_Cost/Comp_TP)*100))+'%'   AS [Comp_Cost%]
            //FROM
            //(SELECT (CASE PROD_LINE WHEN 'Laminate' THEN '生产-桌子' WHEN 'Panel' THEN '生产-屏风' 
            //WHEN 'Seating' THEN '生产-椅子' WHEN 'MCG' THEN '生产-MCG' ELSE PROD_LINE END) AS COST_FACTORY,
            //SUM(Inside_Qty*TP_PRICE) AS Comp_TP
            //FROM
            //(SELECT MO_NO,SUM(Scn_Qty) AS Inside_Qty 
            //FROM Transfer_101_Inside_record
            //WHERE Scn_Date>='" + strDate_Start + @"' AND Scn_Date<='" + strDate_End + @"'
            //GROUP BY MO_NO) AS a
            //LEFT JOIN Transfer_Package_MO_TP AS b
            //ON a.MO_NO=b.AUFNR
            //GROUP BY PROD_LINE) AS e
            //LEFT JOIN
            //(SELECT (CASE c.COST_FACTORY WHEN 'MCG' THEN '生产-MCG' ELSE c.COST_FACTORY END) AS COST_FACTORY,
            //Salary_Cost,Extra_Cost,Salary_Cost+Extra_Cost AS Comp_Cost
            //FROM
            //(SELECT COST_FACTORY,SUM(NET_PAY_COST) AS Salary_Cost
            //FROM Cost_Labor_Comp_Daily
            //WHERE COST_DATE>='" + strDate_Start + @"' AND COST_DATE<='" + strDate_End + @"'
            //GROUP BY COST_FACTORY) AS c
            //LEFT JOIN
            //(SELECT COST_FACTORY,SUM(SI_COST+HPF_COST) AS Extra_Cost
            //FROM Cost_Social_Insurance_Monthly
            //WHERE COST_MONTH='" + strHNI_Month + @"'
            //GROUP BY COST_FACTORY) AS d
            //ON c.COST_FACTORY=d.COST_FACTORY) AS f
            //ON e.COST_FACTORY=f.COST_FACTORY "
            //+ strCondition;

            //            dt = Lamex.Base.BaseBuildSql.GetDataTableBySql(strSql);

            #endregion

            strSql = @"SELECT isnull(e.COST_FACTORY,'无分厂TP') COST_FACTORY,convert(int,Salary_Cost) Salary_Cost,convert(int,Extra_Cost) Extra_Cost,convert(int,Comp_Cost) Comp_Cost,convert(int,Comp_TP) Comp_TP,    convert( varchar,Convert(decimal(18,2),( Comp_Cost/Comp_TP)*100))+'%'   AS [Comp_Cost%]
FROM
(SELECT (CASE PROD_LINE WHEN 'Laminate' THEN '生产-桌子' WHEN 'Panel' THEN '生产-屏风' 
WHEN 'Seating' THEN '生产-椅子' WHEN 'MCG' THEN 'MCG' ELSE PROD_LINE END) AS COST_FACTORY,
SUM(Inside_Qty*TP_PRICE) AS Comp_TP
FROM
(SELECT MO_NO,SUM(Scn_Qty) AS Inside_Qty 
FROM [172.23.134.36].[LamexMESManager].[dbo].[Transfer_101_Inside_record]
WHERE Scn_Date>='" + strDate_Start + @"' AND Scn_Date<='" + strDate_End + @"'
GROUP BY MO_NO) AS a
LEFT JOIN Transfer_Package_MO_TP AS b
ON a.MO_NO=b.AUFNR
GROUP BY PROD_LINE) AS e
LEFT JOIN
(SELECT (CASE c.COST_FACTORY WHEN 'MCG' THEN 'MCG' ELSE c.COST_FACTORY END) AS COST_FACTORY,
Salary_Cost,Extra_Cost,isnull(Salary_Cost,0)+isnull(Extra_Cost,0) AS Comp_Cost
FROM
(SELECT COST_FACTORY,SUM(NET_PAY_COST) AS Salary_Cost
FROM Cost_Labor_Comp_Daily
WHERE COST_DATE>='" + strDate_Start + @"' AND COST_DATE<='" + strDate_End + @"'
GROUP BY COST_FACTORY) AS c
LEFT JOIN
(SELECT COST_FACTORY,SUM(SI_COST+HPF_COST) AS Extra_Cost
FROM Cost_Social_Insurance_Monthly
WHERE COST_MONTH='" + strHNI_Month + @"'
GROUP BY COST_FACTORY) AS d
ON c.COST_FACTORY=d.COST_FACTORY) AS f
ON e.COST_FACTORY=f.COST_FACTORY"
+ strCondition + @"
ORDER BY COST_FACTORY";

            DataTable dt = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(strSql);

            if (dt.Rows.Count > 0)
            {
                dtChart = dt;
                gridControl1.DataSource = dt;
                gridView1.OptionsBehavior.Editable = false;
                gridView1.OptionsView.ColumnAutoWidth = true;
            }
        }

        private void addGridData_By_Detail()
        {
            //            SELECT   a.Scn_Date AS 日期, b.PROD_LINE AS 分厂, SUM(a.Scn_Qty * b.TP_PRICE) AS 每日入仓TPTP,null AS 每日人工成本
            //FROM      dbo.Transfer_101_Inside_record AS a LEFT OUTER JOIN
            //                dbo.Transfer_Package_MO_TP AS b ON a.MO_NO = b.AUFNR
            //GROUP BY a.Scn_Date, b.PROD_LINE

            //取[日期]，查[Cost_Labor_Comp_Daily],返回[NET_PAY_COST]
            //取[日期]中的年月，查[Cost_Social_Insurance_Monthly]及[Cost_Labor_Comp_Daily],返回[SI_COST+HPF_COST+]/30
            //以上[NET_PAY_COST]+[SI_COST+HPF_COST+]/30=[每日人工成本]

            //            SELECT   a.Scn_Date AS 日期, b.PROD_LINE AS 分厂, SUM(a.Scn_Qty * b.TP_PRICE) AS 每日入仓TP,null AS 每日人工成本, 
            //null AS 累计入仓TP,null AS 累计人工成本
            //FROM      dbo.Transfer_101_Inside_record AS a LEFT OUTER JOIN
            //                dbo.Transfer_Package_MO_TP AS b ON a.MO_NO = b.AUFNR
            //GROUP BY a.Scn_Date, b.PROD_LINE

            string sql = @"select g.idate 日期,'" + comboBoxEdit1.Text + @"' as 分厂,isnull(g.Scn_Qty,0) 每日入仓TP,isnull(c.AvgDay,0) 每日人工成本,0.0 AS 累计入仓TP,0.0 AS 累计人工成本 ,'' as [Comp Cost%],0.0 as [Comp Cost], 1 as Flag
                            from ( SELECT d.idate,a.COST_FACTORY,a.Scn_Date,a.PROD_LINE,a.Scn_Qty FROM DateRange('d','" + strDate_Start + @"','" + strDate_End + @"')  d
                            left join (
                            select * from (
                            SELECT CASE b.PROD_LINE WHEN 'Laminate' THEN '生产-桌子' WHEN 'Panel' THEN '生产-屏风' 
                            WHEN 'Seating' THEN '生产-椅子' WHEN 'MCG' THEN 'MCG' ELSE b.PROD_LINE END AS COST_FACTORY, a.Scn_Date , b.PROD_LINE , SUM(a.Scn_Qty * b.TP_PRICE) Scn_Qty
                            FROM      [172.23.134.36].[LamexMESManager].[dbo].Transfer_101_Inside_record AS a LEFT OUTER JOIN
                            dbo.Transfer_Package_MO_TP AS b ON a.MO_NO = b.AUFNR
                            GROUP BY a.Scn_Date, b.PROD_LINE) n 
                            where n.COST_FACTORY='生产-屏风'
                            ) a on a.Scn_Date=d.idate) g
                            left  outer join  (
                           select f.idate,f.COST_FACTORY,isnull(NET_PAY_COST,0)+isnull(f.AvgNum,0) as AvgDay from (

                    select b.idate,b.COST_FACTORY,(SI_COST+HPF_COST)/30 as AvgNum from(
                    SELECT d.idate,c.* FROM DateRange('d','" + strDate_Start + @"','" + strDate_End + @"') d
                     left join  
                    Cost_Social_Insurance_Monthly c on c.COST_MONTH=d.iYM 
                    where c.COST_FACTORY='生产-屏风' 
                    ) b ) f
                    left join Cost_Labor_Comp_Daily a on f.idate=a.COST_DATE and f.COST_FACTORY=a.COST_FACTORY
                    where f.COST_FACTORY='生产-屏风' 
                             )
                            c on c.idate=g.idate  where 1!=1";
            DataTable dtTemp = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(sql);
            dtTemp.Columns.Add("日期1");
            string strWhere = "";
            if(comboBoxEdit1.Text!="全厂")
            {
                string strSql = @"select g.idate 日期,'" + comboBoxEdit1.Text + @"' as 分厂,isnull(g.Scn_Qty,0) 每日入仓TP,isnull(c.AvgDay,0) 每日人工成本,0.0 AS 累计入仓TP,0.0 AS 累计人工成本 ,'' as [Comp Cost%],0.0 as [Comp Cost], 1 as Flag
                        from ( SELECT d.idate,a.COST_FACTORY,a.Scn_Date,a.PROD_LINE,a.Scn_Qty FROM DateRange('d','" + strDate_Start + @"','" + strDate_End + @"')  d
                        left join (
                        select * from (
                        SELECT CASE b.PROD_LINE WHEN 'Laminate' THEN '生产-桌子' WHEN 'Panel' THEN '生产-屏风' 
                        WHEN 'Seating' THEN '生产-椅子' WHEN 'MCG' THEN 'MCG' ELSE b.PROD_LINE END AS COST_FACTORY, a.Scn_Date , b.PROD_LINE , SUM(a.Scn_Qty * b.TP_PRICE) Scn_Qty
                        FROM      [172.23.134.36].[LamexMESManager].[dbo].Transfer_101_Inside_record AS a LEFT OUTER JOIN
                        dbo.Transfer_Package_MO_TP AS b ON a.MO_NO = b.AUFNR
                        GROUP BY a.Scn_Date, b.PROD_LINE) n 
                        where n.COST_FACTORY='" + comboBoxEdit1.Text + @"'
                        ) a on a.Scn_Date=d.idate) g
                        left  outer join  (
                        select a.COST_DATE,a.COST_FACTORY,isnull(NET_PAY_COST,0)+isnull(f.AvgNum,0) as AvgDay from 
						Cost_Labor_Comp_Daily a 
						
                    left join (select b.idate,b.COST_FACTORY,(SI_COST+HPF_COST)/30 as AvgNum from(
                    SELECT d.idate,c.* FROM DateRange('d','" + strDate_Start + @"','" + strDate_End + @"') d
                     left join  
                    Cost_Social_Insurance_Monthly c on c.COST_MONTH=d.iYM 
                    where c.COST_FACTORY='" + comboBoxEdit1.Text + @"' 
                    ) b) f on f.idate=a.COST_DATE and f.COST_FACTORY=a.COST_FACTORY
                    where a.COST_FACTORY='" + comboBoxEdit1.Text + @"' 
                            )
                        c on c.COST_DATE=g.idate ";

                DataTable dt = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(strSql);
                dt.Columns.Add("日期1");
                if (dt.Rows.Count != 0)
                {
                    
                    decimal cumSumRMB = 0;
                    decimal cumSumTP = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        dr["日期1"] = Convert.ToDateTime(dr["日期"].ToString()).ToString("MM-dd");
                        dr["每日入仓TP"] =Convert.ToInt32( Convert.ToDecimal(dr["每日入仓TP"].ToString()));
                        dr["每日人工成本"] = Convert.ToInt32(Convert.ToDecimal(dr["每日人工成本"].ToString()));
                        cumSumTP = cumSumTP + Convert.ToInt32(Convert.ToDecimal(dr["每日入仓TP"].ToString()));
                        cumSumRMB = cumSumRMB + Convert.ToInt32(Convert.ToDecimal(dr["每日人工成本"].ToString()));
                        dr["累计入仓TP"] =(int) cumSumTP;
                        dr["累计人工成本"] = (int)cumSumRMB;
                        if (cumSumTP != 0)
                            dr["Comp Cost%"] = Math.Round(Math.Round(cumSumRMB / cumSumTP, 4) * 100, 2) + "%";
                        else
                            dr["Comp Cost%"] = 0 + "%";
                        if (cumSumTP != 0)
                            dr["Comp Cost"] = Math.Round(cumSumRMB / cumSumTP, 4);
                        else
                            dr["Comp Cost"] = 0.0;
                        if (checkEdit1.Checked == true)
                        {
                            if (decimal.Parse(dr["每日入仓TP"].ToString()) == 0&& decimal.Parse(dr["每日人工成本"].ToString()) == 0)
                                dr["Flag"] = 0;
                            else
                                dr["Flag"] = 1;
                        }
                        else
                            dr["Flag"] = 1;
                    }
                }
                dtTemp = dt;
                
                if (checkEdit1.Checked == true)
                {
                    if(dt.Select(" Flag<>0").Count()!=0)
                    {
                        DataTable dtZore = dt.Select(" Flag<>0").CopyToDataTable();
                        dtChart = dtZore;
                        gridControl2.DataSource = dtZore;
                    }
                }
                else
                {
                    dtChart = dt;
                    gridControl2.DataSource = dt;
                }
            }
            else
            {
                
                foreach (string s in comboBoxEdit1.Properties.Items)
                {
                    if (s != "全厂")
                    {
                        string strSql = @"select g.idate 日期,'" + s + @"' as 分厂,isnull(g.Scn_Qty,0) 每日入仓TP,isnull(c.AvgDay,0) 每日人工成本,0.0 AS 累计入仓TP,0.0 AS 累计人工成本 ,'' as [Comp Cost%],0.0 as [Comp Cost], 1 as Flag
                        from ( SELECT d.idate,a.COST_FACTORY,a.Scn_Date,a.PROD_LINE,a.Scn_Qty FROM DateRange('d','" + strDate_Start + @"','" + strDate_End + @"')  d
                        left join (
                        select * from (
                        SELECT CASE b.PROD_LINE WHEN 'Laminate' THEN '生产-桌子' WHEN 'Panel' THEN '生产-屏风' 
                        WHEN 'Seating' THEN '生产-椅子' WHEN 'MCG' THEN 'MCG' ELSE b.PROD_LINE END AS COST_FACTORY, a.Scn_Date , b.PROD_LINE , SUM(a.Scn_Qty * b.TP_PRICE) Scn_Qty
                        FROM      [172.23.134.36].[LamexMESManager].[dbo].Transfer_101_Inside_record AS a LEFT OUTER JOIN
                        dbo.Transfer_Package_MO_TP AS b ON a.MO_NO = b.AUFNR
                        GROUP BY a.Scn_Date, b.PROD_LINE) n 
                        where n.COST_FACTORY='" + s + @"'
                        ) a on a.Scn_Date=d.idate) g
                        left  outer join  (
                        select a.COST_DATE,a.COST_FACTORY,isnull(NET_PAY_COST,0)+isnull(f.AvgNum,0) as AvgDay from 
						Cost_Labor_Comp_Daily a 
						
                    left join (select b.idate,b.COST_FACTORY,(SI_COST+HPF_COST)/30 as AvgNum from(
                    SELECT d.idate,c.* FROM DateRange('d','" + strDate_Start + @"','" + strDate_End + @"') d
                     left join  
                    Cost_Social_Insurance_Monthly c on c.COST_MONTH=d.iYM 
                    where c.COST_FACTORY='" + s + @"' 
                    ) b) f on f.idate=a.COST_DATE and f.COST_FACTORY=a.COST_FACTORY
                    where a.COST_FACTORY='" + s + @"' 
                            )
                        c on c.COST_DATE=g.idate ";

                        DataTable dt = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(strSql);
                        decimal cumSumRMB = 0;
                        decimal cumSumTP = 0;
                        dt.Columns.Add("日期1");
                        foreach (DataRow dr in dt.Rows)
                        {

                            DataRow d = dtTemp.NewRow();
                            dr["日期1"] = Convert.ToDateTime(dr["日期"].ToString()).ToString("MM-dd");
                            dr["每日入仓TP"] = Convert.ToInt32(Convert.ToDecimal(dr["每日入仓TP"].ToString()));
                            dr["每日人工成本"] = Convert.ToInt32(Convert.ToDecimal(dr["每日人工成本"].ToString()));
                            cumSumTP = cumSumTP + Convert.ToInt32(Convert.ToDecimal(dr["每日入仓TP"].ToString()));
                            cumSumRMB = cumSumRMB + Convert.ToInt32(Convert.ToDecimal(dr["每日人工成本"].ToString()));
                            dr["累计入仓TP"] = cumSumTP;
                            dr["累计人工成本"] = cumSumRMB;
                            if (cumSumTP != 0)
                                dr["Comp Cost%"] = Math.Round(Math.Round(cumSumRMB / cumSumTP, 4)*100,2)  + "%";
                            else
                                dr["Comp Cost%"] = 0 + "%";
                            if (cumSumTP != 0)
                                dr["Comp Cost"] = Math.Round(cumSumRMB / cumSumTP, 4);
                            else
                                dr["Comp Cost"] = 0.0;

                            if (checkEdit1.Checked == true)
                            {
                                if (decimal.Parse(dr["每日入仓TP"].ToString()) == 0 && decimal.Parse(dr["每日人工成本"].ToString()) == 0)
                                    dr["Flag"] = 0;
                                else
                                    dr["Flag"] = 1;
                            }
                            else
                                dr["Flag"] = 1;

                            d.ItemArray = dr.ItemArray;
                            dtTemp.Rows.Add(d);

                            
                        }

                    }
                }
                if(checkEdit1.Checked == true)
                {
                    if (dtTemp.Select(" Flag<>0").Count() != 0)
                    {
                        DataTable dtZore = dtTemp.Select("Flag<>0").CopyToDataTable();
                        dtChart = dtZore;
                        gridControl2.DataSource = dtZore;
                    }
                }
                else
                {
                    dtChart = dtTemp;
                    gridControl2.DataSource = dtTemp;
                }
                
            }
            

            //if (comboBoxEdit1.Text!="全厂")
            //    strWhere = @"where a.COST_FACTORY='" + comboBoxEdit1.Text+ "'";
            //if (!string.IsNullOrEmpty(strWhere))
            //    strSql += strWhere;
            //strSql += " GROUP BY a.Scn_Date, a.PROD_LINE,c.AvgDay ";
            //    DataTable dt = BaseBuildSql.GetDataTableBySql(strSql);
            //gridControl2.DataSource = dt;
            



        }

        //表页脚设置
        private void TotalSum()
        {
            if (gridView1.RowCount > 0)
            {
                gridView1.Columns[0].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
                gridView1.Columns[0].SummaryItem.DisplayFormat = "合计：";

                for (int i = 1; i < gridView1.Columns.Count; i++)
                {
                    gridView1.Columns[i].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                    gridView1.Columns[i].SummaryItem.DisplayFormat = "{0:N}";
                    // gridView1.Columns["部件数量"].Width = 80;
                }
            }
        }

        #region 用户界面初始化

        private void radioGroup1_SelectedIndexChanged(object sender, EventArgs e)
        {
            resetUI();
        }

        //获取HNI月对应的起止日期 
        private void dateEdit1_EditValueChanged(object sender, EventArgs e)
        {
            //根据dateEdit1值，通过表[Cost_HNI_Calendar]，查询返回起止日期
            //首先判断数据库是否有dateEdit1.Text，再取TOP 2
            if (string.IsNullOrEmpty(dateEdit1.Text))
            {
                dateEdit2.EditValue = null;
                dateEdit3.EditValue = null;
                resetUI();
                return;
            }

            DataTable dt1 = Lamex.Common.DataBaseHelper_41.GetDataTableBySql("select top 2 * from [172.23.134.36].[LamexMESManager].[dbo].Cost_HNI_Calendar where YEAR_MONTH='" + dateEdit1.Text + "'  order by YEAR_MONTH");////AAA

            if(dt1.Rows.Count==0)
            {
                dateEdit2.EditValue = null;
                dateEdit3.EditValue = null;
                strDate_Start = "";
                strDate_End = "";
                return;
            }
            DataTable dt = Lamex.Common.DataBaseHelper_41.GetDataTableBySql("select top 2 * from [172.23.134.36].[LamexMESManager].[dbo].Cost_HNI_Calendar where YEAR_MONTH>='" + dateEdit1.Text + "'  order by YEAR_MONTH");////AAA
            //DataTable dt = Lamex.Base.BaseBuildSql.GetDataTableBySql("select top 2 * from Cost_HNI_Calendar where YEAR_MONTH>'" + dateEdit1.Text + "'");
            if (dt.Rows.Count == 2)
            {

                strDate_Start = dt.Rows[0]["FIRST_DATE"].ToString();
                strDate_End = Convert.ToDateTime(dt.Rows[1]["FIRST_DATE"].ToString()).AddDays(-1).ToString("yyyy-MM-dd");
                dateEdit2.DateTime = Convert.ToDateTime(strDate_Start);
                dateEdit3.DateTime = Convert.ToDateTime(strDate_End); ;
            }
            else 
            {
                XtraMessageBox.Show("无法获取起始日或结束日，请核实！");
                dateEdit2.EditValue = null;
                dateEdit3.EditValue = null;
                return;
            }
           
                resetUI();
        }

        private void comboBoxEdit1_SelectedIndexChanged(object sender, EventArgs e)
        {
            resetUI();
        }

        private void checkEdit1_CheckedChanged(object sender, EventArgs e)
        {
            //sbtnRefresh_Click(null, null);

            resetUI();
        }

        private void 导出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Lamex.Common.ExecelHelper.GridViewToExcel(gridView2);
        }

        private void resetUI()
        {
            gridControl1.DataSource = null;
            gridControl2.DataSource = null;

            chartControl1.Series.Clear();
            chartControl1.DataSource = null;
        }

        #endregion

        private void ShowChart()
        {
            if (dtChart == null) return;
            chartControl1.Series.Clear();
            DataView dataView = dtChart.DefaultView;
            DataTable dataTableDistinct = dataView.ToTable(true, "分厂");
            DataTable disFactory = dtChart.Clone();

            decimal cumSumRMB = 0;
            decimal cumSumTP = 0;
            if (dataTableDistinct.Rows.Count>1)
            {
                
                DataTable dataList = dataView.ToTable(true, "日期");
                dataList = dataList.Select(" 1=1 ", "日期").CopyToDataTable();
                foreach (DataRow dr in dataList.Rows)
                {
                    DataRow drow = disFactory.NewRow();
                    DataTable temp = dtChart.Select(" 日期='" + dr["日期"].ToString() + "'").CopyToDataTable();
                    decimal sumTP = (decimal) temp.Compute("sum(每日入仓TP)", "TRUE");
                    decimal sumRmb = (decimal)temp.Compute("sum(每日人工成本)", "TRUE");
                    cumSumTP = cumSumTP + sumTP;
                    cumSumRMB = cumSumRMB + sumRmb;
                    drow["日期"] = dr["日期"];
                    drow["日期1"] = Convert.ToDateTime(dr["日期"].ToString()).ToString("MM-dd");
                    drow["每日入仓TP"] = sumTP;
                    drow["每日人工成本"] = sumRmb;
                    drow["累计入仓TP"] = cumSumTP;
                    drow["累计人工成本"] = cumSumRMB;
                    if (cumSumTP != 0)
                        drow["Comp Cost%"] = Math.Round(cumSumRMB / cumSumTP, 4) * 100 + "%";
                    else
                        drow["Comp Cost%"] = 0 + "%";
                    if (cumSumTP != 0)
                        drow["Comp Cost"] = Math.Round(cumSumRMB / cumSumTP, 4);
                    else
                        drow["Comp Cost"] = 0.0;
                    disFactory.Rows.Add(drow);
                }
            }
            int maxValue = 0;
            Series seriesline = new Series("Comp Cost%", ViewType.Line);
            Series series = new Series("每日入仓TP", ViewType.Bar);
            if (dataTableDistinct.Rows.Count>1)
            {
                
                series.DataSource = disFactory;
                chartControl1.Series.Add(series);
                SetSeries(series, "日期1", new string[] { "每日入仓TP" });
                BarSeriesView barView1 = (BarSeriesView)series.View;
                barView1.Color = Color.Green;




                //foreach (DataRow drow in dtChart.Rows)
                //{
                //    SeriesPoint p = new SeriesPoint();

                //    double[] d = new double[] { double.Parse(drow["Comp Cost"].ToString()) };
                //    p.Values = d;

                //    p.Argument = double.Parse(drow["Comp Cost"].ToString()) * 100.0 + "%";
                //    p.DateTimeArgument= drow["日期1"].ToString();
                //    seriesline.Points.Add(p);
                //}
                seriesline.DataSource = disFactory;

                chartControl1.Series.Add(seriesline);
                SetSeries(seriesline, "日期1", new string[] { "Comp Cost" });
                seriesline.CrosshairLabelPattern = "{ A: yyyy - M - d}：{V:0.00%}";
                LineSeriesView barView = (LineSeriesView)seriesline.View;
                barView.Color = Color.Blue;

                maxValue = int.Parse(disFactory.Compute("Max(每日入仓TP)", "true").ToString());

            }
            else
            {
               
                series.DataSource = dtChart;
                chartControl1.Series.Add(series);
                SetSeries(series, "日期1", new string[] { "每日入仓TP" });
                BarSeriesView barView1 = (BarSeriesView)series.View;
                barView1.Color = Color.Green;

                //foreach (DataRow drow in dtChart.Rows)
                //{
                //    SeriesPoint p = new SeriesPoint();

                //    double[] d = new double[] { double.Parse(drow["Comp Cost"].ToString()) };
                //    p.Values = d;

                //    p.Argument = double.Parse(drow["Comp Cost"].ToString()) * 100.0 + "%";
                //    p.DateTimeArgument = Convert.ToDateTime(drow["日期1"].ToString());
                    
                //    seriesline.Points.Add(p);
                //}

                seriesline.DataSource = dtChart;
                chartControl1.Series.Add(seriesline);
                SetSeries(seriesline, "日期1", new string[] { "Comp Cost" });
                seriesline.CrosshairLabelPattern = "{ A: yyyy - M - d}：{V:0.00%}";
                LineSeriesView barView = (LineSeriesView)seriesline.View;
                barView.Color = Color.Blue;
                maxValue = int.Parse(dtChart.Compute("Max(每日入仓TP)", "true").ToString());

            }


            ((LineSeriesView)seriesline.View).MarkerVisibility = DefaultBoolean.True;
            series.LabelsVisibility = DefaultBoolean.False;
            seriesline.LabelsVisibility = DefaultBoolean.False;
            //setting show percent 
            seriesline.PointOptions.ValueNumericOptions.Format = NumericFormat.Percent;
            //seriesline.PointOptions.ValueNumericOptions.Format = NumericFormat.Percent;
            //add y axis show
            XYDiagram diagramhour = (XYDiagram)chartControl1.Diagram;
            diagramhour.SecondaryAxesY.Clear();
            SecondaryAxisY AxisY = new SecondaryAxisY();
            //AxisY.NumericScaleOptions.GridSpacing = 100000;

            //如果要分开控制两个Y轴显示范围，则不可以启用WholeRange及VisualRange
            //AxisY.WholeRange.MaxValue = maxValue;
            //AxisY.WholeRange.MinValue = 0;
            //AxisY.VisualRange.MaxValue = maxValue;
            //AxisY.VisualRange.MinValue = 0;
            AxisY.Title.Text = "每日入仓TP";
            AxisY.Title.Alignment = StringAlignment.Far;
            AxisY.Title.Visibility = DefaultBoolean.True;
            AxisY.VisualRange.SideMarginsValue = 0;
            diagramhour.SecondaryAxesY.Add(AxisY);
            ((BarSeriesView)series.View).AxisY = AxisY;

            //以下设置为最大值，并不是MaxValue = 0.3
            //diagramhour.AxisY.VisualRange.MaxValue = 0.3;
            //diagramhour.AxisY.VisualRange.SideMarginsValue = 0;

            //设置左侧Y轴按范围显示
            diagramhour.AxisY.WholeRange.SetMinMaxValues(0, 0.3);
           
            //设置左侧Y轴按百分比显示
            diagramhour.AxisY.Label.TextPattern = "{V:0.00%}";
            diagramhour.AxisY.Title.Text = "Comp Cost%";
            diagramhour.AxisY.Title.Alignment = StringAlignment.Far;
            diagramhour.AxisY.Title.Visibility = DefaultBoolean.True;

            //series title positive setting
            Legend Legends = (Legend)chartControl1.Legend;
            Legends.Visibility = DevExpress.Utils.DefaultBoolean.True;
            Legends.AlignmentVertical = LegendAlignmentVertical.TopOutside;
            Legends.AlignmentHorizontal = LegendAlignmentHorizontal.Center;
            Legends.Direction = LegendDirection.LeftToRight;
            Legends.TextVisible = true;


            diagramhour.AxisY.ConstantLines.Clear();

            ConstantLine constantLineligh1 = new ConstantLine("目标" + trackBarControl1 .Value+ "%",Math.Round( double.Parse(trackBarControl1.Value.ToString())/100.0,2));
            
            constantLineligh1.Color = Color.Red; //直线颜色
            constantLineligh1.Title.TextColor = Color.Red;   //直线文本字体颜色  
            constantLineligh1.Title.Alignment = ConstantLineTitleAlignment.Near;
            constantLineligh1.Title.Visible = true;
            constantLineligh1.ShowInLegend = false;
            diagramhour.AxisY.ConstantLines.Add(constantLineligh1);

            //diagramhour.AxisY.WholeRange.MaxValue = 1;
            //diagramhour.AxisY.WholeRange.MinValue = 0;

            //ConstantLine constantLineMax = new ConstantLine("MAX:" +1.2, 1.2);
            //constantLineMax.Color = Color.Red; //直线颜色
            //constantLineMax.Title.TextColor = Color.Red;   //直线文本字体颜色    
            //constantLineMax.Title.Visible = true;
            //constantLineMax.ShowInLegend = false;
            //diagramhour.AxisY.ConstantLines.Add(constantLineMax);

            //ConstantLine constantLineMin = new ConstantLine("MIN:" + 0, 0);
            //constantLineMin.Color = Color.Gray; //直线颜色
            //constantLineMin.Title.TextColor = Color.Gray;   //直线文本字体颜色    
            //constantLineMin.Title.Visible = true;
            //constantLineMin.ShowInLegend = false;
            //diagramhour.AxisY.ConstantLines.Add(constantLineMin);

            #region 设置系列值为百分比显示
            //设置数据显示形式(Percent:百分比,Currency:货币类型，数据前添加￥,Scientific:科学计数法)
            chartControl1.Series[0].Label.PointOptions.ValueNumericOptions.Format = NumericFormat.Percent;
            //chartControl1.Series[0].Label.TextPattern = "{V:0%}";

            //数据是否保留小数(0：不保留小数位，1保留一位小数，2保留两位小数)
            chartControl1.Series[0].Label.PointOptions.ValueNumericOptions.Precision = 1;
            
            #endregion

            // 横标题设置
            chartControl1.Titles.Clear();
            ChartTitle titles = new ChartTitle();            //声明标题
            titles.Text = "Comp Cost% Trend";                            //名称
                                                                  //titles.TextColor = System.Drawing.Color;   //颜色
            titles.Indent = 1;                                //设置距离  值越小柱状图就越大
            titles.Font = new Font("Tahoma", 12, FontStyle.Bold);            //设置字体
            titles.Dock = ChartTitleDockStyle.Top;           //设置对齐方式
            titles.Indent = 0;
            titles.Alignment = StringAlignment.Center;       //居中对齐
            chartControl1.Titles.Add(titles);                 //添加标题  
        }

        private void rangeTrackBarControl1_EditValueChanged(object sender, EventArgs e)
        {
            labelControl6.Text = "(" + trackBarControl1.Value.ToString() + "%)";
            ShowChart();
        }

        private void frmCOST_Component_Analysis_FormClosed(object sender, FormClosedEventArgs e)
        {
            Lamex.Common.IniFile ini = new Lamex.Common.IniFile(Application.StartupPath + "\\config\\SavePath.ini");
            ini.IniWriteValue("ScaleRecord", "Record",trackBarControl1.Value.ToString());
        }

        /// <summary>
        /// 设置Series 的属性
        /// </summary>
        /// <param name="ser"></param>
        /// <param name="argumentDataMember"></param>
        /// <param name="valueDataMember"></param>
        private void SetSeries(Series series, string argumentDataMember, string[] valueDataMember)
        {
            // setting X  axis type
            series.ArgumentScaleType = ScaleType.Qualitative;
            //show information and data
            series.PointOptions.PointView = PointView.Values;
            // setting X axis bind field
            series.ArgumentDataMember = argumentDataMember;
            series.ValueScaleType = ScaleType.Numerical;
            // setting Y axis bind field
            series.ValueDataMembers.AddRange(valueDataMember);
            // show lable data
            series.Label.Visible = true;
        }

        public override void DoView(IButtonInfo sender)
        {
            sbtnView_Click(null, null);
            base.DoView(sender);
        }

        public override void DoExport(IButtonInfo sender)
        {
            导出ToolStripMenuItem_Click(null, null);
            base.DoExport(sender);
        }
    }
}